﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DA
{
    public class CongNhanDA:BaseConnection
    {
        public SqlDataAdapter danhSachCongNhanAdapter {set;get;}
        public CongNhanDA()
        {
           
        }


        public DataSet layDanhSachCongNhan()
        {
            openConnect();
            String sqlStm = "SELECT * FROM CongNhan";

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            danhSachCongNhanAdapter = new SqlDataAdapter(cmd);
            danhSachCongNhanAdapter.SelectCommand = cmd;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(danhSachCongNhanAdapter);
            DataSet ds = new DataSet();
            danhSachCongNhanAdapter.Fill(ds, "CongNhan");
            closeConnect();
            return ds;         
           
        }

        public DataSet layDanhSachMaVaTenCongNhan()
        {
            String sqlStm = "  SELECT TenCongNhan + ' - [' + MaCongNhan + ']' AS TenCongNhan, MaCongNhan FROM CongNhan";
            return layDuLieuTheoCauLenhSQL(sqlStm);
        }

        public DataSet timKiemCongNhan(int loaiTimKiem, String strTimKiem)
        {
            DataSet ds = new DataSet();
            String sqlStatement = "";
            if(loaiTimKiem == 1){
                sqlStatement = "SELECT MaCongNhan, TenCongNhan, BacCongNhan";
		        sqlStatement += " FROM CongNhan";
                sqlStatement += " WHERE MaCongNhan = '" + strTimKiem + "'";
		
            }else if(loaiTimKiem == 2){
                sqlStatement = " SELECT MaCongNhan, TenCongNhan, BacCongNhan";
                sqlStatement += " FROM CongNhan";
                sqlStatement += " WHERE TenCongNhan like N'%" + strTimKiem + "%'";
            }else if(loaiTimKiem ==3){
                sqlStatement = " SELECT MaCongNhan, TenCongNhan, BacCongNhan";
                sqlStatement += " FROM CongNhan";
                sqlStatement += " WHERE BacCongNhan = '" + strTimKiem + "'";
            }
            openConnect();
         
            
            SqlCommand cmd = new SqlCommand(sqlStatement, con);
            danhSachCongNhanAdapter = new SqlDataAdapter(cmd);
            danhSachCongNhanAdapter.SelectCommand = cmd;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(danhSachCongNhanAdapter);
            danhSachCongNhanAdapter.Fill(ds, "CongNhan");
            closeConnect();
            return ds;         
        }
        public DataSet chonCongNhan()
        {
            DataSet ds = new DataSet();

            //SqlDataReader myReader = null;
            String sqlStatement = "";
            sqlStatement = "SELECT TenCongNhan + ' - [' + MaCongNhan + ']' AS TenCongNhan, MaCongNhan FROM CongNhan ";

            openConnect();

            SqlCommand cmd = new SqlCommand(sqlStatement, con);
            danhSachCongNhanAdapter = new SqlDataAdapter(cmd);
            danhSachCongNhanAdapter.SelectCommand = cmd;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(danhSachCongNhanAdapter);
            danhSachCongNhanAdapter.Fill(ds, "CongNhan");
            closeConnect();
            return ds;  
        }
    }
}
